package org.liaohailong.library.db

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.text.TextUtils
import org.liaohailong.library.db.builder.SQLHelperFactory
import org.liaohailong.library.db.model.OldTableInfo
import org.liaohailong.library.db.util.Utility

/**
 * Author: liaohailong
 * Date: 2019/4/3
 * Time: 4:00 PM
 * Description: 数据库操作实例
 **/
class VictorSQLiteOpenHelper(context: Context, dbName: String, dbVersion: Int) :
        SQLiteOpenHelper(context, dbName, null, dbVersion) {
    override fun onCreate(db: SQLiteDatabase?) {
        //建表
        createTableIfNotExist(db)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        //更新表
        migrate(db)
    }

    /**
     * 建表
     */
    private fun createTableIfNotExist(db: SQLiteDatabase?) {
        db?.run {
            for (table in SQLite.getTables()) {
                SQLHelperFactory.get().apply {
                    setTable(table)
                    val sql = getCreateTableIfNotExistsSQL()
                    execSQL(sql)
                }
            }
        }
    }

    private fun migrate(db: SQLiteDatabase?) {
        //step1 找到所有旧表
        val existTable = getExistTable(db)
        //step2 所有旧表重命名为临时表
        val tempTable = renameTableToTemp(db, existTable)
        //step3 重新创建所有表
        createTableIfNotExist(db)
        //step4 临时表数据迁移到新表
        transferTempToNewTable(db, tempTable)
        //step5 删除临时表
        dropTempTable(db, tempTable)
    }

    private fun getExistTable(db: SQLiteDatabase?): List<OldTableInfo> {
        val existTableList = mutableListOf<OldTableInfo>()
        db?.run {
            for (table in SQLite.getTables()) {
                SQLHelperFactory.get().apply {
                    setTable(table)
                    val tableName = getTable()

                    val sql = checkTableIfExistSQL()
                    val cursor = rawQuery(sql, null)
                    val tableExist = cursor.columnNames.isNotEmpty()
                    cursor.close()

                    if (tableExist) {
                        val oldTableInfo = OldTableInfo(tableName)
                        existTableList.add(oldTableInfo)
                    }
                }
            }
        }
        return existTableList
    }

    private fun renameTableToTemp(db: SQLiteDatabase?, existTables: List<OldTableInfo>): List<OldTableInfo> {
        val tempTableList = mutableListOf<OldTableInfo>()
        db?.run {
            for (table in SQLite.getTables()) {
                SQLHelperFactory.get().apply {
                    setTable(table)
                    val tableName = getTable()

                    for (existTable in existTables) {
                        if (TextUtils.equals(existTable.name, tableName)) {
                            val tempName = tableName + "_temp"
                            val sql = getRenameTableToTempSQL(tempName)
                            execSQL(sql)
                            existTable.tempName = tempName
                            tempTableList.add(existTable)
                            break
                        }
                    }
                }
            }
        }
        return tempTableList
    }

    private fun transferTempToNewTable(db: SQLiteDatabase?, tempTables: List<OldTableInfo>) {
        db?.run {
            for (tempTable in tempTables) {
                val oldTableName = tempTable.name
                val tempName = tempTable.tempName
                for (table in SQLite.getTables()) {
                    val newTable = Utility.getTableInfo(table)
                    if (TextUtils.equals(newTable.name, oldTableName)) {
                        //找出新表迁入字段
                        val newTableCursor = query(newTable.name, null, null, null, null, null, null)
                        val newColumnNames = newTableCursor.columnNames
                        newTableCursor.close()
                        //找出旧表迁出字段
                        val oldTableCursor = query(tempName, null, null, null, null, null, null)
                        val oldColumnNames = oldTableCursor.columnNames
                        oldTableCursor.close()

                        //命中新表，数据迁移
                        SQLHelperFactory.get().apply {
                            setTable(table)
                            val primaryKey = getPrimaryKey()
                            tempTable.columns.clear()
                            for (newColumnName in newColumnNames) {
                                when {
                                    TextUtils.equals(primaryKey, newColumnName) -> tempTable.columns.add("null")
                                    oldColumnNames.contains(newColumnName) -> tempTable.columns.add(newColumnName)
                                    else -> tempTable.columns.add("\"\"")
                                }
                            }
                            val sql = getTransferTableSQL(tempTable.columns, tempName)
                            execSQL(sql)
                        }
                    }
                }
            }
        }
    }

    private fun dropTempTable(db: SQLiteDatabase?, tempTables: List<OldTableInfo>) {
        db?.run {
            for (tempTable in tempTables) {
                SQLHelperFactory.get().apply {
                    val sql = getDropTableSQL(tempTable.tempName)
                    execSQL(sql)
                }
            }
        }
    }
}